/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package vn.webapp.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import vn.webapp.dto.DataPage;
import vn.webapp.model.Classes;
import vn.webapp.model.Timetabling;
import vn.webapp.model.Timetablingraw;

@Repository("timetablingDAO")
@SuppressWarnings({"unchecked", "rawtypes"})
@Transactional
public class TimetablingDAOImpl extends BaseDao implements TimetablingDAO {

    @Autowired
    private SessionFactory sessionFactory;

    @Override
    public List<Timetabling> getAll(){
    
        try {
            begin();
            Criteria criteria = getSession().createCriteria(Timetabling.class, "timetabling");
            criteria.createAlias("timetabling.coursing", "coursing");
            criteria.createAlias("timetabling.rooms", "rooms");
            
            List<Timetabling> timetables = criteria.list();
            commit();
            return timetables;
        } catch (HibernateException e) {
            e.printStackTrace();
            rollback();
            close();
            return null;
        } finally {
            flush();
            close();
        }
    }
    
    @Override
    public List<Timetabling> list() {
        try {
            begin();
            Criteria criteria = getSession().createCriteria(Timetabling.class, "timetabling");
            criteria.createAlias("timetabling.coursing", "coursing");
            criteria.createAlias("timetabling.rooms", "rooms");
            
            List<Timetabling> timetables = criteria.list();
            commit();
            return timetables;
        } catch (HibernateException e) {
            e.printStackTrace();
            rollback();
            close();
            return null;
        } finally {
            flush();
            close();
        }
    }
    
    @Override
    public List<Timetablingraw> listRaw() {
        try {
            begin();
            Criteria criteria = getSession().createCriteria(Timetablingraw.class, "timetablingRaw");
            //criteria.createAlias("timetablingRaw.coursing", "coursing");
            criteria.createAlias("timetablingRaw.rooms", "rooms");
            
            List<Timetablingraw> timetables = criteria.list();
            commit();
            return timetables;
        } catch (HibernateException e) {
            e.printStackTrace();
            rollback();
            close();
            return null;
        } finally {
            flush();
            close();
        }
    }
    
    @Override
    public Timetablingraw checkExists(int id, int coursingId, int roomId, int day, int slot, int class_id){
        try {
            begin();
            Criteria criteria = getSession().createCriteria(Timetabling.class, "timetabling");
            criteria.add(Restrictions.eq("timetabling.courseteacher_id",coursingId));
            criteria.add(Restrictions.eq("timetabling.room_id",roomId));
            criteria.add(Restrictions.eq("timetabling.day",day));
            criteria.add(Restrictions.eq("timetabling.slot",slot));
            criteria.add(Restrictions.eq("timetabling.class_id",class_id));
            criteria.add(Restrictions.ne("timetabling.timetabling_id",id));
            Timetablingraw timetabling = (Timetablingraw)criteria.uniqueResult();
            commit();
            return timetabling;
        } catch (HibernateException e) {
            e.printStackTrace();
            rollback();
            close();
            return null;
        } finally {
            flush();
            close();
        }
    }
            
    @Override
    public int removeATimetabling(int id){
        Timetabling c = new Timetabling();
        c.setTimetabling_id(id);        
        try {
            begin();
            getSession().delete(c);
            commit();
            return 1;
        } catch (HibernateException e) {
            e.printStackTrace();
            rollback();
            close();
            return 0;
        } finally {
            flush();
            close();
        }
    }
    
    @Override
    public int saveATimetabling(Timetablingraw timetabling)
    {
        try {
           begin();
           int id = 0; 
           id = (int)getSession().save(timetabling);
           commit();
           return id;           
        } catch (HibernateException e) {
            e.printStackTrace();
            rollback();
            close();
            return 0;
        } finally {
            flush();
            close();
        }
    }
    
    @Override
    public void editATimetabling(Timetablingraw timetabling){
        try {
           begin();
           getSession().update(timetabling);
           commit();
        } catch (HibernateException e) {
            e.printStackTrace();
            rollback();
            close();
        } finally {
            flush();
            close();
        }
    }
    
    @Override
    public List<Timetabling> filter(int page, int pageSize) {
        try {
            begin();
            Criteria criteria = getSession().createCriteria(Timetabling.class);
            //criteria.createAlias("timetabling.coursing", "coursing");
            //criteria.createAlias("timetabling.rooms", "rooms");
            
            //Criteria criteria = getSession().createCriteria(Timetabling.class);
            criteria.setMaxResults(pageSize);
            if (page > 0) {
                criteria.setFirstResult(page * pageSize);
            } else {
                criteria.setFirstResult(page);
            }
            List<Timetabling> timetabling = criteria.list();
            commit();
            return timetabling;
        } catch (HibernateException e) {
            e.printStackTrace();
            rollback();
            close();
            return null;
        } finally {
            flush();
            close();
        }
    }
    
    @Override
    public int count(){
        //int size = 0;
         try {
            begin();
            Criteria criteria = getSession().createCriteria(Timetabling.class);            
            List<Timetabling> timetabling = criteria.list();
            commit();
            return timetabling.size();
        } catch (HibernateException e) {
            e.printStackTrace();
            rollback();
            close();
            return 0;
        } finally {
            flush();
            close();
        }
    }
    
    @Override
    public int truncateOldData()
    {
        try {
            begin();
//            Query query = sessionFactory.getCurrentSession().createQuery("TRUNCATE timetabling");
//            int result = query.executeUpdate();
            
            Query query = getSession().createQuery("TRUNCATE TABLE timetabling ");
            int result = query.executeUpdate();
        
            commit();
        return result;
        } catch (HibernateException e) {
            e.printStackTrace();
            rollback();
            close();
            return 0;
        } finally {
            flush();
            close();
        }
    }
    
}
